/*
 * The model is independent of the user interface. 
 * It doesn't know if it's being used from a text-based, graphical, or web interface. 
 * This is the same model used in the presentation example.
 */
package javaidiot;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;


/**
 *
 * @author Master
 */
public class Model {

    private SQLConnection db;

    public Model(SQLConnection db) {
        this.db = db;
    }

    private static String date() {
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Calendar cal = Calendar.getInstance();
        return dateFormat.format(cal.getTime());

    }

    public void addProduct(String name) {
        addProduct(name, "0");
    }

    public void addProduct(String name, String price) {
        String sql = "";
        sql = "INSERT INTO Product(ProductName) VALUES ('" + name + "');";
        db.insert(sql);
        sql = "SELECT TOP 1 ProductID FROM Product ORDER BY ProductID DESC;";
        db.select(sql);
        if (db.select.resultArray != null) {
            try {
                String id = db.select.resultArray[0][0];
                sql = "INSERT INTO Price(ProductID, PriceDate, Price) VALUES (" + id + ", '" + date() + "', " + String.valueOf(Float.valueOf(price)) + ");";
                db.insert(sql);
            } catch (NullPointerException e) {
            }
        }
    }

    public void addEmployee(String name) {
        addEmployee(name, "");
    }

    public void addEmployee(String name, String phone) {
        String sql = "";
        sql = "INSERT INTO Employees(EmployeeName, PhoneNumber) VALUES ('" + name + "', '" + phone + "');";
        db.insert(sql);
        sql = "SELECT TOP 1 EmployeeID FROM Employees ORDER BY EmployeeID DESC;";
        db.select(sql);
    }

    public void selectProductList() {
        String sql = "";
        sql = "SELECT n.ProductID, n.ProductName, j.PriceDate, h.Price FROM Product AS n"
                + " JOIN (SELECT ProductID, MAX(PriceDate) as PriceDate FROM Price GROUP BY ProductID) AS j"
                + " ON j.ProductID = n.ProductID"
                + " JOIN Price as h on h.ProductID = n.ProductID AND h.PriceDate = j.PriceDate"
                + ";";
        db.select(sql);
    }

    public void selectProductByName(String name) {
        String sql = "";
        sql = "SELECT n.ProductID, n.ProductName, j.PriceDate, h.Price FROM Product AS n"
                + " JOIN (SELECT ProductID, MAX(PriceDate) as PriceDate FROM Price GROUP BY ProductID) AS j"
                + " ON j.ProductID = n.ProductID"
                + " JOIN Price as h on h.ProductID = n.ProductID AND h.PriceDate = j.PriceDate"
                + " WHERE n.ProductName LIKE '%" + name + "%'"
                + ";";
        db.select(sql);
    }

    public void selectProductById(String id) {
        String sql = "";
        sql = "SELECT n.ProductID, n.ProductName, j.PriceDate, h.Price FROM Product AS n"
                + " JOIN (SELECT ProductID, MAX(PriceDate) as PriceDate FROM Price GROUP BY ProductID) AS j"
                + " ON j.ProductID = n.ProductID"
                + " JOIN Price as h on h.ProductID = n.ProductID AND h.PriceDate = j.PriceDate"
                + " WHERE n.ProductID = " + id
                + ";";
        db.select(sql);
    }

    public void selectEmployeeList() {
        String sql = "SELECT EmployeeID, EmployeeName, PhoneNumber FROM Employees;";
        db.select(sql);
    }

    public void selectEmployeeByName(String name) {
        String sql = "SELECT EmployeeID, EmployeeName, PhoneNumber FROM Employees"
                + " WHERE EmployeeName LIKE '%" + name + "%'"
                + " OR PhoneNumber LIKE '%" + name + "%'"
                + ";";
        db.select(sql);
    }

    public void selectEmployeeById(String id) {
        String sql = "SELECT EmployeeID, EmployeeName, PhoneNumber FROM Employees"
                + " WHERE EmployeeID = " + id
                + ";";
        db.select(sql);
    }

    public void selectOrderList() {
        String sql = "";
        sql = "SELECT Orders.OrderID, Orders.OrderDate, Employees.EmployeeName FROM Orders"
                + " LEFT JOIN OrderExecutor ON OrderExecutor.OrderID = Orders.OrderID"
                + " LEFT JOIN Employees ON Employees.EmployeeID = OrderExecutor.EmployeeID"
                + ";";
        db.select(sql);
    }

    public void selectOrderDetails(String id) {
        String sql = "";
        sql = "SELECT OrderDetails.ProductID, Product.ProductName, OrderDetails.Quantity"
                + " FROM OrderDetails"
                + " LEFT JOIN Product ON Product.ProductID = OrderDetails.ProductID"
                + " WHERE OrderDetails.OrderID = " + id
                + ";";
        db.select(sql);
    }

    public void selectOrderDetailsFull(String id) {
        String sql = "";
        sql = ""
                + "SELECT OrderDetails.ProductID, Product.ProductName, OrderDetails.Quantity,"
                + " 	("
                + " 	SELECT Employees.EmployeeName "
                + " 	FROM Employees"
                + " 	WHERE Employees.EmployeeID = "
                + " 		("
                + " 		SELECT EmployeeID"
                + " 		FROM OrderExecutor"
                + " 		WHERE OrderExecutor.OrderID = " + id
                + " 		)"
                + " 	) AS EmployeeName, Orders.OrderDate, Orders.OrderID"
                + " FROM OrderDetails "
                + " LEFT JOIN Product ON Product.ProductID = OrderDetails.ProductID"
                + " LEFT JOIN Orders ON Orders.OrderID = OrderDetails.OrderID"
                + " WHERE OrderDetails.OrderID = " + id
                + ";";
        db.select(sql);
    }

    public void selectOrderAdditionalProductList(String id) {
        String sql = "";
        sql = "SELECT Product.ProductID, Product.ProductName"
                + " FROM Product"
                + " WHERE Product.ProductID NOT IN "
                + " ("
                + " 	SELECT OrderDetails.ProductID"
                + " 	FROM OrderDetails"
                + " 	WHERE OrderDetails.OrderID = " + id
                + " )"
                + ";";
        db.select(sql);
    }

    public void selectOrderByEmployee(String name) {
        String sql = "";
        sql = "SELECT Orders.OrderID, Orders.OrderDate, Employees.EmployeeName FROM Orders"
                + " LEFT JOIN OrderExecutor ON OrderExecutor.OrderID = Orders.OrderID"
                + " LEFT JOIN Employees ON Employees.EmployeeID = OrderExecutor.EmployeeID"
                + " WHERE Employees.EmployeeName LIKE '" + name + "'"
                + ";";
        db.select(sql);
    }

    public void selectOrderById(String id) {
        String sql = "";
        sql = "SELECT Orders.OrderID, Orders.OrderDate, Employees.EmployeeName FROM Orders"
                + " LEFT JOIN OrderExecutor ON OrderExecutor.OrderID = Orders.OrderID"
                + " LEFT JOIN Employees ON Employees.EmployeeID = OrderExecutor.EmployeeID"
                + " WHERE Orders.OrderID = " + id
                + ";";
        db.select(sql);
    }

    public void selectOrderByProduct(String name) {
        String sql = "";
        sql = "SELECT Orders.OrderID, Orders.OrderDate, Employees.EmployeeName FROM Orders"
                + " LEFT JOIN OrderExecutor ON OrderExecutor.OrderID = Orders.OrderID"
                + " LEFT JOIN Employees ON Employees.EmployeeID = OrderExecutor.EmployeeID"
                + " LEFT JOIN OrderDetails ON OrderDetails.OrderID = Orders.OrderID"
                + " LEFT JOIN Product ON Product.ProductID = OrderDetails.ProductID"
                + " WHERE Product.ProductName LIKE '%" + name + "%'"
                + ";";
        db.select(sql);
    }

    public void selectOrderByDate(String[] d) {
        String sql = "";
        String w = " WHERE ";
        String a = " AND ";
        boolean b = false;
        sql = "SELECT Orders.OrderID, Orders.OrderDate, Employees.EmployeeName FROM Orders"
                + " LEFT JOIN OrderExecutor ON OrderExecutor.OrderID = Orders.OrderID"
                + " LEFT JOIN Employees ON Employees.EmployeeID = OrderExecutor.EmployeeID";
        if (!d[0].equalsIgnoreCase("*")) {
            sql += ((b) ? a : w) + "datepart(year,Orders.OrderDate) = " + d[0];
            b = true;
        }
        if (!d[1].equalsIgnoreCase("*")) {
            sql += ((b) ? a : w) + "datepart(month,Orders.OrderDate) = " + d[1];
            b = true;
        }
        if (!d[2].equalsIgnoreCase("*")) {
            sql += ((b) ? a : w) + "datepart(day,Orders.OrderDate) = " + d[2];
            b = true;
        }
        sql += ";";
        System.out.println(sql);
        db.select(sql);
    }

    public void deleteProduct(String id) {
        String sql = "";
        sql = "DELETE FROM Price WHERE ProductID = " + id + ";";
        db.delete(sql);
        sql = "DELETE FROM Product WHERE ProductID = " + id + ";";
        db.delete(sql);
    }

    public void deleteEmployee(String id) {
        String sql = "";
        sql = "DELETE FROM Employees WHERE EmployeeID = " + id + ";";
        db.delete(sql);
    }

    public void deleteOrder(String id) {
        String sql = "";
        sql = "DELETE FROM OrderExecutor WHERE OrderID = " + id + ";";
        db.delete(sql);
        sql = "DELETE FROM OrderDetails WHERE OrderID = " + id + ";";
        db.delete(sql);
        sql = "DELETE FROM Orders WHERE OrderID = " + id + ";";
        db.delete(sql);
    }

    public void updateProduct(String id, String name, String price) {
        String sql = "";
        sql = "UPDATE Product SET ProductName = '" + name + "' WHERE ProductID = " + id + ";";
        db.update(sql);
        sql = "INSERT INTO Price(ProductID, PriceDate, Price) VALUES (" + id + ", '" + date() + "', " + String.valueOf(Float.valueOf(price)) + ");";
        db.insert(sql);
    }

    public void updateEmployee(String id, String name, String phone) {
        String sql = "";
        sql = "UPDATE Employees SET EmployeeName = '" + name + "', PhoneNumber = '" + phone + "' WHERE EmployeeID = " + id + ";";
        db.update(sql);
    }

    public void updateOrderProductAmount(String orderId, String productId, String amount) {
        String sql = "";
        sql = "UPDATE OrderDetails SET Quantity = '" + String.valueOf(Integer.parseInt(amount)) + "' WHERE ProductID = " + productId + " AND OrderID = " + orderId + ";";
        db.update(sql);
    }

    public void updateOrderProductRemove(String orderId, String productId) {
        String sql = "";
        sql = "DELETE FROM OrderDetails WHERE OrderID = " + orderId + " AND ProductID = " + productId;
        db.delete(sql);
    }

    public void updateOrderProductAppend(String orderId, String productId) {
        String sql = "";
        sql = "INSERT INTO OrderDetails(OrderID, ProductID) VALUES (" + orderId + ", " + productId + ");";
        db.insert(sql);
    }

    public void updateOrderSetEmployee(String orderId, String empeeId) {
        String sql = "";
        sql = "UPDATE OrderExecutor SET EmployeeID = " + empeeId + " WHERE OrderID = " + orderId;
        db.update(sql);
    }
}
